//
//  WmcDBManager.swift
//  YPWatermarkCamera
//
//  Created by 赵新 on 2022/3/25.
//

import FMDB
import Foundation
import YPBedrock

extension WmcDBManager {
    /// 属性
    struct DataType {
        enum SqlType: String {
            case integer, real, varchar, blob
        }

        var name: String
        var sqlType: SqlType
        var isNotNull: Bool = true
        var defaultValue: String? = nil
        var isPrimaryKeyAutoIncrement: Bool = false

        var setting_sqlStr: String {
            var sqlStr = "\"\(name)\" \(sqlType.rawValue)"
            if isNotNull {
                sqlStr.append(" NOT NULL")
            }
            if let defaultValue = defaultValue {
                sqlStr.append(" DEFAULT \(defaultValue)")
            }
            if isPrimaryKeyAutoIncrement {
                sqlStr.append(" PRIMARY KEY AUTOINCREMENT")
            }
            return sqlStr
        }

        var add_column_sqlStr: String {
            let sqlStr = "\"\(name)\" \(sqlType.rawValue)"
            return sqlStr
        }

        /// 主键
        static func primaryKey() -> DataType {
            let dataType: DataType = .init(name: "id",
                                           sqlType: .integer,
                                           isNotNull: true,
                                           defaultValue: "0",
                                           isPrimaryKeyAutoIncrement: true)
            return dataType
        }

        /// 浮点
        static func real(name: String) -> DataType {
            let dataType: DataType = .init(name: name,
                                           sqlType: .real,
                                           defaultValue: "0.0")
            return dataType
        }

        /// 文本
        static func varchar(name: String) -> DataType {
            let dataType: DataType = .init(name: name,
                                           sqlType: .varchar,
                                           defaultValue: nil)
            return dataType
        }

        /// 数据
        static func blob(name: String) -> DataType {
            let dataType: DataType = .init(name: name,
                                           sqlType: .blob,
                                           defaultValue: nil)
            return dataType
        }

        /// 长整型
        static func integer(name: String) -> DataType {
            let dataType: DataType = .init(name: name,
                                           sqlType: .integer,
                                           defaultValue: "0")
            return dataType
        }

        /// 时间
        static func date(name: String) -> DataType {
            let dataType: DataType = .init(name: name,
                                           sqlType: .integer,
                                           defaultValue: nil)
            return dataType
        }
    }

    struct InsertData {
        var column: DataType
        var value: Any
    }

    /// 日志系统
    fileprivate func log(_ log: String) {
        WmcLogManager.DebugLog(type: .dbManager, log: log)
    }
}

// MARK: - [WmcDBProtocol]

protocol WmcDBProtocol {
    static var tableName: String { get }
    static var dataType: [WmcDBManager.DataType] { get }
    /// 数据库迁移
    static func migration()
    /// 数据库迁移回调
    static func setMigrationCompleteBlock(_ block: @escaping YPBlock.Empty)
}

extension WmcDBProtocol {
    /// 数据库迁移
    static func migration() {}
    /// 数据库迁移回调
    static func setMigrationCompleteBlock(_ block: @escaping YPBlock.Empty) {}
}

// MARK: - [WmcDBManager]

class WmcDBManager {
    /// 单例实例化
    static let shared: WmcDBManager = {
        let manager = WmcDBManager()
        return manager
    }()

    /// 文件管理对象
    let fileManager: FileManager = .default

    /// 数据库操作线程
    let dataBaseActionQueue: DispatchQueue = .init(label: "wmc.queue.WmcDBManager")

    /// Documents目录地址
    let systemDocumentsPath: String = YPSystem.Documents.Path

    /// 数据库目录名
    let dataBaseDir: String = "WmcDataBase"

    /// 数据库名
    let mainDataBaseName: String = "mainDataBase.sqlite"

    /// 主数据库目录
    lazy var mainDataBasePath: String = {
        let str = systemDocumentsPath
            .yp.appendPathComponent(dataBaseDir)
        return str
    }()

    /// 主数据库
    lazy var mainDataBase: FMDatabase = {
        let mainDBPath = mainDataBasePath
            .yp.appendPathComponent(mainDataBaseName)
        let dataBase = FMDatabase(path: mainDBPath)
        return dataBase
    }()

    func config() {
        // 链接数据库
        linkMainDB()

        // 创建水印数据缓存表
        registerMap(WmcWatermarkListDBManager.self)
        // 创建本地相册表(新)
        registerMap(WmcLocalMediaAlbumDBManager.self)
        // 创建用户数据表
        registerMap(WmcUserInfoDBManager.self)
        // 创建备注历史记录数据表
        registerMap(WmcRemarkHistoryDBManager.self)
        // 用户编辑过的水印信息表
        registerMap(WmcUserEditWatermarksDBManager.self)
        // 常用地址数据表
        registerMap(WmcCommonlyAddressDBManager.self)
        // 自定义地址
        registerMap(WmcCustomAddressDBManager.self)
        // 偏好设置
        registerMap(WmcUserSettingsDBManager.self)
        // 通用设置
        registerMap(WmcCommonSettingsDBManager.self)
        // 相册信息
        registerMap(WmcCloudAlbumInfoDBManager.self)

        // 有需要迁移的数据库自行迁移
        mapMigration()
    }

    /// 表迁移
    func mapMigration() {
        // 本地相册表迁移
        WmcLocalMediaAlbumDBManager.migration()
    }

    // TODO: 之后会将该方法迁移到各个数据表中管理
    /// 更新查询表
    func createUploadQueueTable() {
        let tableName = WmcUploadQueueDBManager.tableName
        if !tableName.isEmpty {
            createTable(tableName: tableName,
                        dataType: WmcUploadQueueDBManager.dataType)
        }
    }

    func createUploadQueueV2Table() {
        let tableName = WmcUploadQueueV2DBManager.tableName
        if !tableName.isEmpty {
            createTable(tableName: tableName,
                        dataType: WmcUploadQueueV2DBManager.dataType)
        }
    }
    
    func createCloudAblumInfoTable() {
        let tableName = WmcCloudAlbumInfoDBManager.tableName
        if !tableName.isEmpty {
            createTable(tableName: tableName,
                        dataType: WmcCloudAlbumInfoDBManager.dataType)
        }
    }

    // TODO: --------------

    /// 注册数据表
    func registerMap(_ map: WmcDBProtocol.Type) {
        createTable(tableName: map.tableName,
                    dataType: map.dataType)
    }

    /// 连接主数据库
    func linkMainDB() {
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            // 读取/创建数据库目录
            do {
                try weakSelf.fileManager.yp.createDirectory(weakSelf.mainDataBasePath)
            } catch let err {
                weakSelf.log(" 主数据库目录创建失败！ \(err.localizedDescription) ")
            }
            weakSelf.log("尝试连接主数据库..")
            let isOpen = weakSelf.mainDataBase.open()
            #if DEBUG
                if isOpen {
                    weakSelf.log("主数据库连接成功！")
                } else {
                    weakSelf.log("主数据库连接失败！")
                }
                weakSelf.log("主数据库目录：\(weakSelf.mainDataBasePath)")
            #endif
        }
    }

    /// 创建表
    func createTable(tableName: String,
                     dataType: [DataType],
                     resultBlock: YPBlock.OneParam<Bool>? = nil)
    {
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            // 尝试获取表
            let SQL = "select * from \(tableName)"
            if let tableResults = try? weakSelf.mainDataBase.executeQuery(SQL, values: nil) {
                let columnCount = tableResults.columnCount
                if columnCount < dataType.count { // 当前数据库列小于新列，需要更新
                    weakSelf.log("发现\"\(tableName)\" 表需要更新列")
                    // 查找需要更新的列
                    var updateColumns: [DataType] = []
                    for columnData in dataType {
                        var isContains = false
                        for i in 0 ..< columnCount {
                            if let name = tableResults.columnName(for: i),
                               name == columnData.name
                            {
                                isContains = true
                            }
                        }
                        // 需要添加的新列
                        if !isContains {
                            updateColumns.append(columnData)
                        }
                    }
                    let columnSQL = updateColumns.map { $0.add_column_sqlStr }.yp.joint(",")
                    let updateTableSql = "alter table \(tableName) add column \(columnSQL)"
                    do {
                        try weakSelf.mainDataBase.executeUpdate(updateTableSql, values: nil)
                        weakSelf.log("主数据库 \"\(tableName)\" 表数据更新成功！\n\tupdateColumns:\(updateColumns)")
                    } catch let error {
                        weakSelf.log("主数据库 \"\(tableName)\" 表数据删除失败！\n\terror:\(error.localizedDescription)")
                    }
                }
            }

            weakSelf.log("主数据库尝试建 \"\(tableName)\" 表..（已创建过的不会重复创建）")
            let columnSQL = dataType.map { $0.setting_sqlStr }.yp.joint(",")
            let createTableSQL =
                """
                create table if not exists \(tableName) (\(columnSQL));
                """
            let result = weakSelf.mainDataBase.executeStatements(createTableSQL)
            #if DEBUG
                if result {
                    weakSelf.log("主数据库 \"\(tableName)\" 表创建成功！")
                } else {
                    weakSelf.log("主数据库 \"\(tableName)\" 表创建失败！")
                }
            #endif
            YPGCD.Main {
                resultBlock?(result)
            }
        }
    }

    /// 检查表是否存在
    /// - Parameters:
    ///   - tableName: 表名
    ///   - resultBlock: 结果
    func existTable(tableName: String,
                    resultBlock: @escaping YPBlock.OneParam<Bool>)
    {
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            let findSql = "select * from \(tableName)"
            let result = try? weakSelf.mainDataBase.executeQuery(findSql, values: nil)
            let count = result?.columnCount ?? 0
            if count > 0 {
                YPGCD.Main {
                    resultBlock(true)
                }
                weakSelf.log("主数据库检查表存在 \"\(tableName)\" 存在！")
            } else {
                YPGCD.Main {
                    resultBlock(false)
                }
                weakSelf.log("主数据库检查表存在 \"\(tableName)\" 不存在！")
            }
        }
    }

    /// 删除表
    /// - Parameters:
    ///   - tableName: 表名
    ///   - resultBlock: 结果
    func deleteTable(tableName: String,
                     resultBlock: YPBlock.OneParam<Bool>? = nil)
    {
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            let dropSql = "DROP TABLE \(tableName)"
            let result = weakSelf.mainDataBase.executeStatements(dropSql)
            YPGCD.Main {
                resultBlock?(result)
            }
            if result {
                weakSelf.log("主数据库删除表 \"\(tableName)\" 成功！")
            } else {
                weakSelf.log("主数据库删除表 \"\(tableName)\" 失败！")
            }
        }
    }

    /// 查询某张表是否存在某一列
    /// - Parameters:
    ///   - tableName: 表名
    ///   - column: 列名
    ///   - resultBlock: 结果
    func queryColumn(tableName: String,
                     column: String,
                     resultBlock: YPBlock.OneParam<Bool>? = nil)
    {
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            let result = weakSelf.mainDataBase.columnExists(column, inTableWithName: tableName)
            YPGCD.Main {
                resultBlock?(result)
            }
        }
    }

    /// 追加字段
    /// - Parameters:
    ///   - tableName: 表名
    ///   - dataTypes: 字段名
    func additionalColumn(tableName: String, dataTypes: [DataType]) {
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            var array: [DataType] = []
            dataTypes.forEach { dataType in
                weakSelf.queryColumn(tableName: tableName, column: dataType.name, resultBlock: { result in
                    array.append(dataType)
                })
            }

            array.forEach { dataType in
                let sql =
                    """
                    alter table \(tableName) add column '\(dataType.name)' \(dataType.sqlType)
                    """
                do {
                    try weakSelf.mainDataBase.executeUpdate(sql, values: nil)
                    weakSelf.log("主数据库 \"\(tableName)\" \(dataType.sqlType)新增成功！")
                } catch let error {
                    weakSelf.log("主数据库 \"\(tableName)\" \(dataType.sqlType)新增失败！, error: \(error.localizedDescription)")
                }
            }
        }
    }

    /// 插入数据
    func insertData(tableName: String,
                    insertData: [InsertData],
                    resultBlock: YPBlock.OneParam<Bool>? = nil)
    {
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            weakSelf.log("主数据库尝试向 \"\(tableName)\" 表插入数据..")
            #if DEBUG
                let startTime = CFAbsoluteTimeGetCurrent()
            #endif
            var columnNames = ""
            var values = ""
            insertData.forEach { data in
                columnNames.append("\(data.column.name),")
                values.append("?,")
            }
            columnNames = columnNames.yp.removeSuffix(string: ",")
            values = values.yp.removeSuffix(string: ",")
            let insertSQL = "INSERT INTO \(tableName) (\(columnNames)) VALUES (\(values))"
            let result = weakSelf.mainDataBase.executeUpdate(insertSQL,
                                                             withArgumentsIn: insertData.map { $0.value })
            #if DEBUG
                if result {
                    weakSelf.log("主数据库 \"\(tableName)\" 表数据插入成功！")
                } else {
                    let data = insertData.map { "\n\t\($0)" }.joined()
                    weakSelf.log("主数据库 \"\(tableName)\" 表数据插入失败！\n\t数据:\(data)\n\tSql:\(insertSQL)")
                }
                let endTime = CFAbsoluteTimeGetCurrent()
                let performTime = (endTime - startTime) * 1000
                weakSelf.log("主数据库执行时长：\(performTime)毫秒")
            #endif
            YPGCD.Main {
                resultBlock?(result)
            }
        }
    }

    /// 通过id删除表数据
    /// - Parameters:
    ///   - tableName: 表名
    func deleteData(tableName: String,
                    id: Int,
                    resultBlock: YPBlock.OneParam<Bool>? = nil)
    {
        deleteCustomData(tableName: tableName, condition: "where id = \(id)", resultBlock: resultBlock)
    }

    /// 删除表数据
    /// - Parameters:
    ///   - tableName: 表名
    func deleteCustomData(tableName: String,
                          condition: String? = nil,
                          resultBlock: YPBlock.OneParam<Bool>? = nil)
    {
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            weakSelf.log("主数据库尝试向 \"\(tableName)\" 表删除数据..")
            #if DEBUG
                let startTime = CFAbsoluteTimeGetCurrent()
            #endif
            var sql = "delete from \(tableName)"
            if let condition = condition, !condition.isEmpty {
                sql.append(" \(condition)")
            }
            do {
                try weakSelf.mainDataBase.executeUpdate(sql, values: nil)
                weakSelf.log("主数据库 \"\(tableName)\" 表数据删除成功！")
                YPGCD.Main {
                    resultBlock?(true)
                }
            } catch let error {
                weakSelf.log("主数据库 \"\(tableName)\" 表数据删除失败！\n\terror:\(error.localizedDescription)")
                YPGCD.Main {
                    resultBlock?(false)
                }
            }
            #if DEBUG
                let endTime = CFAbsoluteTimeGetCurrent()
                let performTime = (endTime - startTime) * 1000
                weakSelf.log("主数据库执行时长：\(performTime)毫秒")
            #endif
        }
    }

    /// 更新数据
    /// - Parameters:
    ///   - tableName: 表名
    ///   - dataList: 数据
    ///   - resultBlock: 完成
    func updateData(tableName: String,
                    updateData: [InsertData],
                    condition: String? = nil,
                    resultBlock: YPBlock.OneParam<Bool>? = nil)
    {
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            weakSelf.log("主数据库尝试向 \"\(tableName)\" 表更新数据..")
            #if DEBUG
                let startTime = CFAbsoluteTimeGetCurrent()
            #endif
            var sqlStr = ""
            updateData.forEach { data in
                sqlStr.append("\(data.column.name) = ?,")
            }
            sqlStr = sqlStr.yp.removeSuffix(string: ",")
            var updateSql = "UPDATE \(tableName) SET \(sqlStr)"

            if let condition = condition, !condition.isEmpty {
                updateSql.append(" \(condition)")
            }

            let result = weakSelf.mainDataBase.executeUpdate(updateSql,
                                                             withArgumentsIn: updateData.map { $0.value })
            #if DEBUG
                if result {
                    weakSelf.log("主数据库 \"\(tableName)\" 表数据更新成功！")
                } else {
                    let updateSQL = updateData.map { "\n\t\($0)" }.joined()
                    weakSelf.log("主数据库 \"\(tableName)\" 表数据更新失败！\n\tSql:\(updateSQL)")
                }
                let endTime = CFAbsoluteTimeGetCurrent()
                let performTime = (endTime - startTime) * 1000
                weakSelf.log("主数据库执行时长：\(performTime)毫秒")
            #endif
            YPGCD.Main {
                resultBlock?(result)
            }
        }
    }

    /// 分页搜索数据
    func searchData(tableName: String,
                    page: Int,
                    pageSize: Int,
                    isDesc: Bool = false,
                    resultBlock: @escaping YPBlock.OneParam<[[String: Any]]>)
    {
        let offset: Int = (page - 1) * pageSize
        if page < 1 {
            fatalError("Page起始位为1！不可小于等于0")
        }
        var descSQL = ""
        if isDesc {
            descSQL = "ORDER BY \(DataType.primaryKey().name) DESC"
        }
        let condition = "\(descSQL) limit \(pageSize) offset \(offset)"
        searchCustomData(tableName: tableName, condition: condition, resultBlock: resultBlock)
    }

    /// 根据id查询数据
    func searchData(tableName: String,
                    id: Int,
                    resultBlock: @escaping YPBlock.OneParam<[String: Any]?>)
    {
        searchCustomData(tableName: tableName, condition: "where id = \(id)") { list in
            if list.count == 1, let dic = list.first {
                YPGCD.Main {
                    resultBlock(dic)
                }
            }
        }
    }

    /// 自定义条件查询
    func searchCustomData(tableName: String,
                          condition: String? = nil,
                          resultBlock: @escaping YPBlock.OneParam<[[String: Any]]>)
    {
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            weakSelf.log("主数据库开始检索 \(tableName) 表")
            #if DEBUG
                let startTime = CFAbsoluteTimeGetCurrent()
            #endif
            var SQL = "select * from \(tableName)"
            if let condition = condition, !condition.isEmpty {
                SQL.append(" \(condition)")
            }
            guard let results = try? weakSelf.mainDataBase.executeQuery(SQL, values: nil) else {
                weakSelf.log("主数据库 \"\(tableName)\" 表数据搜索失败！")
                resultBlock([])
                return
            }
            let columnCount = results.columnCount
            var resultArray: [[String: Any]] = []
            while results.next() == true {
                // 遍历列数据
                var rawObj: [String: Any] = [:]
                for i in 0 ..< columnCount {
                    if let key = results.columnName(for: i),
                       let value = results.object(forColumnIndex: i)
                    {
                        rawObj[key] = value
                    }
                }
                resultArray.append(rawObj)
            }
            #if DEBUG
                let endTime = CFAbsoluteTimeGetCurrent()
                let performTime = (endTime - startTime) * 1000
                weakSelf.log("主数据库查询执行时长：\(performTime)毫秒\n\t本次查询 condition: \(condition ?? "")\n\t共计 \(resultArray.count) 条数据")
            #endif
            YPGCD.Main {
                resultBlock(resultArray)
            }
        }
    }
}
